package DAO;

import com.mysql.jdbc.JDBC4PreparedStatement;
import entity.User;
import utils.DBHelper;
import utils.MD5;
import java.sql.*;


public class UserDao {

    public User queryLogin(String username, String password) {

        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        String passwordMD5 = MD5.md5(password);

        try {
            pst = connection.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
            pst.setObject(1,username);
            pst.setObject(2,passwordMD5);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                int userID = rs.getInt("userID");
                String name = rs.getString("name");
                String studentID = rs.getString("studentID");
                String email = rs.getString("email");
                String tel = rs.getString("tel");
                String academy = rs.getString("academy");
                User user = new UserDao().queryByUsername(username);
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close();
        }
        return null;
    }

    public User queryRegister(String username,String name,String password,String studentID,String email,String tel,String academy) {

        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        String passwordMD5 = MD5.md5(password);

        if (new UserDao().queryByUsername(username) != null){
            return null;
        }

        try {
            pst = connection.prepareStatement("INSERT INTO `user` (`username`, `name`, `password`, `studentID`, `email`, `tel`, `academy`) VALUES (?, ?, ?, ?, ?, ?, ?)");
            pst.setObject(1,username);
            pst.setObject(2,name);
            pst.setObject(3,passwordMD5);
            pst.setObject(4,studentID);
            pst.setObject(5,email);
            pst.setObject(6,tel);
            pst.setObject(7,academy);

            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            int rs = pst.executeUpdate();

            if (rs == 1) {
                User user = new UserDao().queryByUsername(username);
                return user;
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.close();
        }
        return null;
    }

    public User queryByUsername(String username) {

        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM user WHERE username = ?");
            pst.setObject(1,username);

            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                int userID = rs.getInt("userID");
                String name = rs.getString("name");
                String studentID = rs.getString("studentID");
                String email = rs.getString("email");
                String tel = rs.getString("tel");
                String academy = rs.getString("academy");
                User user = new User(userID,username,name,null,studentID,email,tel,academy);
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public User queryByUserID(int userID) {

        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM user WHERE userID = ?");
            pst.setObject(1,userID);

            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                String username = rs.getString("username");
                String name = rs.getString("name");
                String studentID = rs.getString("studentID");
                String email = rs.getString("email");
                String tel = rs.getString("tel");
                String academy = rs.getString("academy");
                User user = new User(userID,username,name,null,studentID,email,tel,academy);
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        System.out.println(new UserDao().queryByUsername("1"));
    }

}
